package com.yj.ccs.abs.web.asset.mapper;

import com.yj.ccs.abs.web.asset.entity.SettleAccount;
import com.yj.ccs.abs.web.asset.entity.SettleAccountListItem;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Repository;

import java.math.BigDecimal;
import java.util.List;

/**
 * Copyright 2018 (C) Yunjian-VC
 * Created on : 2018/3/22
 * Author     : Unbelievable9
 **/
@Repository
@Mapper
public interface SettleAccountMapper {

    @Options(useGeneratedKeys = true, keyProperty = "item.id")
    @Insert("INSERT INTO `settle_account` (asset_id, date, amount, unit_price, create_time) " +
            "VALUES (#{item.asset_id}, #{item.date}, #{item.amount}, #{item.unit_price}, NOW())")
    void create(@Param("item") SettleAccount item);

    @Update("UPDATE `settle_account` SET date = #{item.date}, amount = #{item.amount}, unit_price = #{item.unit_price} " +
            "WHERE id = #{item.id}")
    void update(@Param("item") SettleAccount item);

    @Delete("DELETE FROM `settle_account` WHERE id = #{id}")
    void deleteById(@Param("id") Long id);

    @Select("SELECT * FROM `settle_account` WHERE id = #{id} LIMIT 1")
    SettleAccount getById(@Param("id") Long id);

    @Select("SELECT COUNT(*) FROM `settle_account` WHERE asset_id = #{asset_id}")
    Integer getCountByAssetId(@Param("asset_id") Long asset_id);

    @Select("SELECT sa.id, sa.date, sa.amount, sa.unit_price, a.url, a.`name`, a.type FROM `settle_account` sa " +
            "LEFT JOIN `attachment` a ON (sa.id = a.reference_id)  " +
            "WHERE a.category = 16 AND sa.asset_id = #{asset_id} " +
            "GROUP BY sa.id")
    List<SettleAccountListItem> getListItemByAssetId(@Param("asset_id") Long asset_id);

    @Select("SELECT SUM(amount) FROM settle_account WHERE asset_id = #{asset_id}")
    BigDecimal getTotalAmountByAssetId(@Param("asset_id") Long asset_id);

    @Select("SELECT SUM(unit_price * amount) FROM settle_account WHERE asset_id = #{asset_id}")
    BigDecimal getTotalPriceByAssetId(@Param("asset_id") Long asset_id);
}
